package dao;

import bean.Fee;
import util.DriverManegerUitl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class FeeDao {
    private Connection conn;
    private PreparedStatement pre;
    private ResultSet resultSet;


    /**
     * 学生端
     * 根据宿舍号查询费用情况
     * @param dormitoryid
     * @return
     */
    //根据宿舍号查询费用情况
    public ArrayList<Fee> getfeeinfoByDormitoruid(String dormitoryid){
        conn= DriverManegerUitl.getConnection();
        ArrayList<Fee> list=new ArrayList<>();
        try {
            pre=conn.prepareStatement("select * from fee where dormitoryid=?");
            pre.setString(1,dormitoryid);
            resultSet=pre.executeQuery();
            while (resultSet.next()){
                Fee fee=new Fee();
                fee.setMonths(resultSet.getString("months"));
                fee.setDormitoryid(resultSet.getString("dormitoryid"));
                fee.setElectricfee(resultSet.getString("electricfee"));
                fee.setElectricnum(resultSet.getString("electricnum"));
                fee.setWaterfee(resultSet.getString("waterfee"));
                fee.setWaternum(resultSet.getString("waternum"));
                list.add(fee);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,pre,resultSet);
        }
        return null;
    }

    /**
     * 管理员端
     * @return
     */
//查找表中所有信息
    public ArrayList<Fee> getAllFeeInfo(){
        conn=DriverManegerUitl.getConnection();
        ArrayList<Fee> list=new ArrayList<>();
        try {
            pre=conn.prepareStatement("select * from fee");
            resultSet= pre.executeQuery();
            while (resultSet.next()){
                Fee fee=new Fee();
                fee.setMonths(resultSet.getString("months"));
                fee.setDormitoryid(resultSet.getString("dormitoryid"));
                fee.setElectricfee(resultSet.getString("electricfee"));
                fee.setElectricnum(resultSet.getString("electricnum"));
                fee.setWaterfee(resultSet.getString("waterfee"));
                fee.setWaternum(resultSet.getString("waternum"));
                list.add(fee);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,pre,resultSet);
        }
        return null;
    }

    /**
     * 修改费用
     * @param fee
     */
//    根据宿舍号，月份修改费用
    public void changeFeeByDormid(Fee fee){
        conn=DriverManegerUitl.getConnection();
        try {
            pre=conn.prepareStatement("update fee set electricfee=?,electricnum=?,waterfee=?,waternum=? where dormitoryid=? and months=?");
            pre.setString(1,fee.getElectricfee());
            pre.setString(2,fee.getElectricnum());
            pre.setString(3,fee.getWaterfee());
            pre.setString(4,fee.getWaternum());
            pre.setString(5,fee.getDormitoryid());
            pre.setString(6,fee.getMonths());
            pre.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,pre,resultSet);
        }
    }
//    提交费用
    public void submit(Fee fee){
        conn=DriverManegerUitl.getConnection();
        try {
            pre=conn.prepareStatement("insert into fee (months,dormitoryid,electricfee,electricnum,waterfee,waternum) value (?,?,?,?,?,?)");
            pre.setString(1,fee.getMonths());
            pre.setString(2,fee.getDormitoryid());
            pre.setString(3,fee.getElectricfee());
            pre.setString(4,fee.getElectricnum());
            pre.setString(5,fee.getWaterfee());
            pre.setString(6,fee.getWaternum());
            pre.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    //管理员删除
//    根据用户名删除信息
    public  void delInfoByMonthAndDormitoryid(String month,String dormitoryid){
        conn=DriverManegerUitl.getConnection();
        try {
            pre=conn.prepareStatement("delete from fee where months=? and dormitoryid=?");
            pre.setString(1,month);
            pre.setString(2,dormitoryid);
            pre.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,pre,resultSet);
        }
    }
}
